﻿---1. LAY THEO CIF HOAC ID_NO
delete from tbl_cust_feedback_raw

--drop table tbl_cust_feedback_raw

insert tbl_cust_feedback_raw
SELECT b.cif, a.Ticket_No,a.CREATOR_NAME,  a.ASSIGNED_TO_NAME, a.LAST_MODIFIED_BY_NAME,a.CreatedTime,a.ModifiedTime,a.Status1, a.Service_Category, a.Service_Sub_Category, a.Description, a.Solution, a.Root_Cause, a.Title, a.EMAIL1, a.Type
  FROM server121.[24_7].[dbo].[Daily_Monitor_CRU] a, TBL_CUSTOMER b
  WHERE 
  (a.VPBCIF =b.CIF OR a.IDNUMBER =b.LEGAL_ID) AND a.REQUEST_FROM like '%Company%' and [Type] in ('Compliment','Complaint')
  ORDER BY ticketid
  
  --select * from server121.[24_7].[dbo].[Daily_Monitor_CRU]

delete from cust_feedback_count

--select * from cust_feedback_count

insert cust_feedback_count
    select * 
from
(select cif, col, value from tbl_cust_feedback_raw
cross apply
( select 'Y', [Type] union all
  select 'Y', status1 ) c(col, value)) d
pivot
( count(col)
  for value in (Complaint,Compliment,Closed,[Open],Cancelled, [Closed-Unsatisfied] )) piv
  order by cif

  delete from TBL_CUSTOMER_FEEDBACK

insert TBL_CUSTOMER_FEEDBACK
  SELECT A.*, B.CUS_NAME, B.BRANCH_ID, B.INDUSTRY_NAME, B.INDUSTRY_NAME_EN, B.DAO, B.DAO_NAME
  FROM CUST_FEEDBACK_COUNT A, TBL_CUSTOMER B
  WHERE A.CIF = B.CIF


--  INSERT TBL_DATE_BUSINESS
--select max(CreatedTime) as date_data, 'TBL_CUSTOMER_FEEDBACK' from tbl_cust_feedback_raw
  
update b
set b.date_data = a.CreatedTime
from (select max(CreatedTime) as CreatedTime from tbl_cust_feedback_raw) a,  TBL_DATE_BUSINESS b
where b.name_table = 'TBL_CUSTOMER_FEEDBACK'